﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using PurchaseSales.Manage.Model.Entities;

namespace PurchaseSales.Manage.Common.SqlHelper
{
    public static class SqlLoginHelper
    {
        private static readonly string ConnectionString = AppConfig.ConnectionString;

        public static string GetUserInDB(string name, string password)
        {
            return Send<string>(string.Format("select top 1 AgentId from Users where AgentId = '{0}' and Password='{1}'", name, password));
        }
        /// <summary>
        /// 获取用户角色的权限
        /// </summary>
        /// <param name="agentid"></param>
        /// <param name="password"></param>
        /// <returns></returns>
        public static IList<string> GetFunctionKey(string agentid, string password)
        {
            var keys = new List<string>();
            var result = ExecuteReader(
                string.Format("select f.FunctionKey from Users as u left join AuthRoles as a on u.RoleId=a.RoleId left join AuthRoleFunction f on u.RoleId=f.RoleId where u.AgentId='{0}' and u.Password='{1}'", agentid, password));
            while (result.Read())
            {
                var name = result.GetValue(0).ToString();
                keys.Add(name);
            }
            return keys;
        }

        public static object Send(string cmd)
        {
            return ExecuteScalar(cmd);
        }

        public static T Send<T>(string cmd)
        {
            var result = Send(cmd);
            if (result == null || Equals(result, DBNull.Value))
                return default(T);
            return (T)result;
        }
        public static object ExecuteScalar(string cmd)
        {
            if (string.IsNullOrEmpty(cmd))
                throw new ArgumentNullException("cmd");
            var conneciton = new SqlConnection(ConnectionString);
            conneciton.Open();
            using (var command = conneciton.CreateCommand())
            {
                command.CommandText = cmd;
                var result = command.ExecuteScalar();
                conneciton.Close();
                return result;
            }
        }
        public static SqlDataReader ExecuteReader(string cmd, string connection)
        {
            var conneciton = new SqlConnection(connection);
            conneciton.Open();
            using (var command = conneciton.CreateCommand())
            {
                command.CommandText = cmd;
                var reader = command.ExecuteReader(CommandBehavior.CloseConnection);
                return reader;
            }
        }

        public static SqlDataReader ExecuteReader(string cmd)
        {
            return ExecuteReader(cmd, ConnectionString);
        }

        public static int ExecuteNonQuery(string cmd)
        {
            if (string.IsNullOrEmpty(cmd))
                throw new ArgumentNullException("cmd");

            var conneciton = new SqlConnection(ConnectionString);
            conneciton.Open();
            using (var command = conneciton.CreateCommand())
            {
                command.CommandText = cmd;
                var result = command.ExecuteNonQuery();
                conneciton.Close();
                return result;
            }
        }
    }
}
